using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NCT.Framework;
using System.Data;
using System.Data.Common;
using NCT.Framework.Database;
using VISS.API.Commons;

namespace VISS.API.Users {
	class UserGeneralConditionDA {

        private const string SP_GetAll = "dbo.UserGeneralCondition_GetAll";
        private const string SP_GetByUserKey = "dbo.UserGeneralCondition_GetByUserKey";
        private const string SP_Insert = "dbo.UserGeneralCondition_Insert";
        private const string SP_Update = "dbo.UserGeneralCondition_Update";

		public static UserGeneralConditionEnt CreateFromReader(IDataReader reader) {
			UserGeneralConditionEnt item = new UserGeneralConditionEnt();
			item.UserKey = ConvertUtils.ToString(reader["UserKey"]);
			item.OldMin = ConvertUtils.ToByte(reader["OldMin"]);
            item.OldMax = ConvertUtils.ToByte(reader["OldMax"]);
			item.HeightMin = ConvertUtils.ToShort(reader["HeightMin"]);
			item.HeightMax = ConvertUtils.ToShort(reader["HeightMax"]);
			item.WeightMin = ConvertUtils.ToShort(reader["WeightMin"]);
			item.WeightMax = ConvertUtils.ToShort(reader["WeightMax"]);
			item.Income = ConvertUtils.ToInt(reader["Income"]);
			item.CityIDs = ConvertUtils.ToString(reader["CityIDs"]);
			item.JobIDs = ConvertUtils.ToString(reader["JobIDs"]);
			item.CountryID = ConvertUtils.ToShort(reader["CountryID"]);
            item.Country = ConvertUtils.ToString(reader["Country"]);
			item.EducationID = ConvertUtils.ToShort(reader["EducationID"]);
            item.Education = ConvertUtils.ToString(reader["Education"]);
			item.ReligionID = ConvertUtils.ToShort(reader["ReligionID"]);
            item.Religion = ConvertUtils.ToString(reader["Religion"]);
			item.MaritalStatusID = ConvertUtils.ToShort(reader["MaritalStatusID"]);
            item.MaritalStatus = ConvertUtils.ToString(reader["MaritalStatus"]);
			item.SmokingID = ConvertUtils.ToShort(reader["SmokingID"]);
            item.Smoking = ConvertUtils.ToString(reader["Smoking"]);
			item.DateCreated = ConvertUtils.ToDateTime(reader["DateCreated"]);
			item.DateModified = ConvertUtils.ToDateTime(reader["DateModified"]);
			return item;
		}

		public static List<UserGeneralConditionEnt> GetAll(string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			List<UserGeneralConditionEnt> result = new List<UserGeneralConditionEnt>();
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_GetAll)) {
				using (IDataReader reader = db.ExecuteReader(cmd)) {
					while (reader.Read()) {
						result.Add(CreateFromReader(reader));
					}
				}
			}
			return result;
		}

        public static UserGeneralConditionEnt GetByUserKey(string userKey, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			UserGeneralConditionEnt result = null;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_GetByUserKey)) {
				db.AddParameter(cmd, "UserKey", userKey);
				using (IDataReader reader = db.ExecuteReader(cmd)) {
					if (reader.Read()) {
						result = CreateFromReader(reader);
                        if (result.CityIDs != "-1") {
                            List<CityEnt> cities = CityDA.GetByRangeID(result.CityIDs, null, null);

                            for (int i = 0; i < cities.Count - 1; i++) {
                                result.Cities += cities[i].Name + ", ";
                            }
                            result.Cities += cities[cities.Count - 1].Name;
                        }

                        if (result.JobIDs != "-1") {
                            List<JobEnt> jobs = JobDA.GetByRangeID(result.JobIDs, null, null);
                            for (int i = 0; i < jobs.Count - 1; i++) {
                                result.Jobs += jobs[i].Name + ", ";
                            }
                            result.Jobs += jobs[jobs.Count - 1].Name;
                        }
					}
				}
			}
			return result;
		}

        public static int Insert(UserGeneralConditionEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_Insert)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "OldMin", item.OldMin);
				db.AddParameter(cmd, "OldMax", item.OldMax);
				db.AddParameter(cmd, "HeightMin", item.HeightMin);
				db.AddParameter(cmd, "HeightMax", item.HeightMax);
				db.AddParameter(cmd, "WeightMin", item.WeightMin);
				db.AddParameter(cmd, "WeightMax", item.WeightMax);
				db.AddParameter(cmd, "Income", item.Income);
				db.AddParameter(cmd, "CityIDs", item.CityIDs);
				db.AddParameter(cmd, "JobIDs", item.JobIDs);
				//db.AddParameter(cmd, "CountryID", item.CountryID);
				db.AddParameter(cmd, "EducationID", item.EducationID);
				db.AddParameter(cmd, "ReligionID", item.ReligionID);
				db.AddParameter(cmd, "MaritalStatusID", item.MaritalStatusID);
				db.AddParameter(cmd, "SmokingID", item.SmokingID);
				db.AddParameter(cmd, "DateCreated", item.DateCreated);
				db.AddParameter(cmd, "DateModified", item.DateModified);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int Update(UserGeneralConditionEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_Update)) {
                db.AddParameter(cmd, "UserKey", item.UserKey);
                db.AddParameter(cmd, "OldMin", item.OldMin);
                db.AddParameter(cmd, "OldMax", item.OldMax);
                db.AddParameter(cmd, "HeightMin", item.HeightMin);
                db.AddParameter(cmd, "HeightMax", item.HeightMax);
                db.AddParameter(cmd, "WeightMin", item.WeightMin);
                db.AddParameter(cmd, "WeightMax", item.WeightMax);
                db.AddParameter(cmd, "Income", item.Income);
                db.AddParameter(cmd, "CityIDs", item.CityIDs);
                db.AddParameter(cmd, "JobIDs", item.JobIDs);
                //db.AddParameter(cmd, "CountryID", item.CountryID);
                db.AddParameter(cmd, "EducationID", item.EducationID);
                db.AddParameter(cmd, "ReligionID", item.ReligionID);
                db.AddParameter(cmd, "MaritalStatusID", item.MaritalStatusID);
                db.AddParameter(cmd, "SmokingID", item.SmokingID);
                db.AddParameter(cmd, "DateModified", item.DateModified);
                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                }
            }
            return result;
        }

	}
}